package com.dp.service.companymanage;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.NoResultException;
import javax.persistence.NonUniqueResultException;
import javax.persistence.Query;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.dp.pojo.JsonPage;
import com.dp.service.impl3.BaseImpl;
import com.dp.util.DataMap;
import com.dp.vo.NewsSnmsg;
import com.dp.vo.TDeviceExtend;
import com.dp.vo.TSpcode;

@Service
@Transactional
public class DeviceextendServiceImpl extends BaseImpl {

	/**
	 * Find all the TDeviceExtend Entity
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<TDeviceExtend>
	 */
	public List<TDeviceExtend> findAllDeviceExtendEntity(DataMap hashMap, Integer firstrow,
			Integer maxrow){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		TDeviceExtend deviceextend=(TDeviceExtend) hashMap.get("deviceextend");
		String spcodes=(String) hashMap.get("spcodes");
		StringBuffer sqlS = new StringBuffer("SELECT t FROM TDeviceExtend t WHERE 1=1 ");
		if(deviceextend!=null){
			if(deviceextend.getSname()!=null&& deviceextend.getSname().toString().trim().length() > 0){
				sqlS.append(" AND t.sname like '%"
						+ deviceextend.getSname().toString().trim() + "%'");
			}
			if(deviceextend.getStel()!=null&& deviceextend.getStel().toString().trim().length() > 0){
				sqlS.append(" AND t.stel like '%"
						+ deviceextend.getStel().toString().trim() + "%'");
			}
			if(deviceextend.getSn()!=null&& deviceextend.getSn().toString().trim().length() > 0){
				sqlS.append(" AND t.sn like '%"
						+ deviceextend.getSn().toString().trim() + "%'");
			}
			if(deviceextend.getSpcode()!=null&&deviceextend.getSpcode().getSpcode()!=null&& deviceextend.getSpcode().getSpcode().toString().trim().length() > 0){
				sqlS.append(" AND t.spcode.spcode='"
						+ deviceextend.getSpcode().getSpcode().toString().trim() + "'");
			}
		}
		if(spcodes!=null)
	    {
			sqlS.append(" AND t.spcode.spcode in ("+spcodes+")");
	    }
		
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				sqlS.append(" order by t."+jpage.getSidx());
				if("spcode".equals(jpage.getSidx())){
					sqlS.append("*1");
				}		
				sqlS.append(" "+jpage.getSord());
			}
		}
		List<TDeviceExtend> queryList=new ArrayList<TDeviceExtend>();
		try {
			Query query = em.createQuery(sqlS.toString());
			if (firstrow != null) {
				query.setFirstResult(firstrow);
			}
			if (maxrow != null) {
				query.setMaxResults(maxrow);
			}
			queryList = query.getResultList();
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}
	
	/**
	 * 学生/员工信息总记录数
	 * @param hashMap
	 * @return Integer
	 */
	public Integer findAllDeviceExtendEntityCount(DataMap hashMap){
		TDeviceExtend deviceextend=(TDeviceExtend) hashMap.get("deviceextend");
		String spcodes=(String) hashMap.get("spcodes");
		Integer iCount = 0; 
			StringBuffer sqlS = new StringBuffer("SELECT count(t.sid) FROM TDeviceExtend t WHERE 1=1 ");
			if(deviceextend!=null){
				if(deviceextend.getSname()!=null&& deviceextend.getSname().toString().trim().length() > 0){
					sqlS.append(" AND t.sname like '%"
							+ deviceextend.getSname().toString().trim() + "%'");
				}
				if(deviceextend.getStel()!=null&& deviceextend.getStel().toString().trim().length() > 0){
					sqlS.append(" AND t.stel like '%"
							+ deviceextend.getStel().toString().trim() + "%'");
				}
				if(deviceextend.getSn()!=null&& deviceextend.getSn().toString().trim().length() > 0){
					sqlS.append(" AND t.sn like '%"
							+ deviceextend.getSn().toString().trim() + "%'");
				}
				if(deviceextend.getSpcode()!=null&&deviceextend.getSpcode().getSpcode()!=null&& deviceextend.getSpcode().getSpcode().toString().trim().length() > 0){
					sqlS.append(" AND t.spcode.spcode='"
							+ deviceextend.getSpcode().getSpcode().toString().trim() + "'");
				}
			}
			if(spcodes!=null)
		    {
				sqlS.append(" AND t.spcode.spcode in ("+spcodes+")");
		    }
		Query query=null;
		try {
			query = em.createQuery(sqlS.toString());
			iCount = Integer.valueOf(query.getSingleResult().toString());
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return iCount;
	}
	
	/**
	 * 新增学生/员工信息
	 * @param deviceextend
	 * @return
	 */
	public Boolean addDeviceExtend(TDeviceExtend deviceextend) {
		Boolean resultb=true;
		String jpql="SELECT t FROM TDeviceExtend t WHERE t.sname=:sname AND t.stel=:stel AND t.sn=:sn AND t.spcode.spcode=:spcode AND t.job=:job AND t.sadress=:sadress AND t.smobile=:smobile ";
		Query query = em.createQuery(jpql);
		query.setParameter("sname",deviceextend.getSname());
		query.setParameter("stel", deviceextend.getStel());
		query.setParameter("sn", deviceextend.getSn());
		query.setParameter("spcode", deviceextend.getSpcode().getSpcode());
		query.setParameter("job", deviceextend.getJob());
		query.setParameter("sadress", deviceextend.getSadress());
		query.setParameter("smobile", deviceextend.getSmobile());
		TDeviceExtend deviceextend_temp= (TDeviceExtend) getSingleResult(query);
		if(deviceextend_temp!=null){
			resultb=null;
		}else{
			if(deviceextend.getSn()!=null&&!"".equals(deviceextend.getSn())){
				jpql="select nc.clientid from NewsClientinfo nc where nc.mencoding='"+deviceextend.getSn()+"' order by nc.clientid asc";
			}else{
				jpql="select (select max(nc.clientid) from TTelDevice ttd_n, NewsClientinfo nc where ttd_n.devicesn=nc.mencoding and ttd_n.telnumber=ttd.telnumber and ttd_n.devicesn=ttd.devicesn)" +
						" from TTelDevice ttd" +
						" where ttd.telnumber='"+deviceextend.getStel()+"' and ttd.telnumber!=''";
			}
			query = em.createQuery(jpql);
			if(query.getResultList().size()==0){
				//是否已经注册：未知
				deviceextend.setHasclientid(0);
			}else{
				Integer clientid=(Integer)query.getResultList().get(0) ;
				if(clientid==null){
					//是否已经注册：否
					deviceextend.setHasclientid(2);
				}else{
					//是否已经注册：是
					deviceextend.setHasclientid(1);
					deviceextend.setClientid(clientid);
				}
			}
			em.persist(deviceextend);
			if(deviceextend.getClientid()!=null){
				//先检查是否存在对应关系
				sql="select clientid from news_groupuserset where groupruleid=(select groupid from t_spcode_device where spcode=?)" +
						" and clientid=? ";
				query=em.createNativeQuery(sql);
				query.setParameter(1, deviceextend.getSpcode().getSpcode());
				query.setParameter(2, deviceextend.getClientid());
				if(query.getResultList().size()==0){
					sql="insert into news_groupuserset (groupruleid,clientid) " +
							"values((select groupid from t_spcode_device where spcode=:spcode),:clientid)";
					query=em.createNativeQuery(sql);
					query.setParameter("spcode", deviceextend.getSpcode().getSpcode());
					query.setParameter("clientid", deviceextend.getClientid());
					query.executeUpdate();
				}
			}
		}
		
		return resultb;
	}
	
	/**
	 * 修改学生/员工信息
	 * @param deviceextend
	 * @return
	 */
	public Boolean updateDeviceExtend(TDeviceExtend deviceextend) {
		Boolean resultb=true;
		String jpql="SELECT t FROM TDeviceExtend t WHERE t.sname=:sname AND t.stel=:stel AND t.sn=:sn AND t.spcode.spcode=:spcode AND t.job=:job AND t.sadress=:sadress AND t.smobile=:smobile AND t.sid!=:sid";
		Query query = em.createQuery(jpql);
		query.setParameter("sname",deviceextend.getSname());
		query.setParameter("stel", deviceextend.getStel());
		query.setParameter("sn", deviceextend.getSn());
		query.setParameter("spcode", deviceextend.getSpcode().getSpcode());
		query.setParameter("job", deviceextend.getJob());
		query.setParameter("sadress", deviceextend.getSadress());
		query.setParameter("smobile", deviceextend.getSmobile());
		query.setParameter("sid", deviceextend.getSid());
		TDeviceExtend deviceextend_temp= (TDeviceExtend) getSingleResult(query);
		if(deviceextend_temp!=null){
			resultb=null;
		}else{
			//保存修改之前的TDeviceExtend
			jpql="SELECT t FROM TDeviceExtend t WHERE t.sid=:sid";
			query = em.createQuery(jpql);
			query.setParameter("sid", deviceextend.getSid());
			TDeviceExtend deviceextend_before = (TDeviceExtend) getSingleResult(query);
			
			jpql="UPDATE TDeviceExtend t SET t.sname=:sname,t.stel=:stel,t.sn=:sn,t.spcode.spcode=:spcode,t.job=:job,t.sadress=:sadress,t.smobile=:smobile WHERE t.sid=:sid";
			query = em.createQuery(jpql);
			query.setParameter("sname",deviceextend.getSname());
			query.setParameter("stel", deviceextend.getStel());
			query.setParameter("sn", deviceextend.getSn());
			query.setParameter("spcode", deviceextend.getSpcode().getSpcode());
			query.setParameter("job", deviceextend.getJob());
			query.setParameter("sadress", deviceextend.getSadress());
			query.setParameter("smobile", deviceextend.getSmobile());
			query.setParameter("sid", deviceextend.getSid());
			query.executeUpdate();
			
			if(!deviceextend.getStel().equals(deviceextend_before.getStel())||!deviceextend.getSn().equals(deviceextend_before.getSn())){
				//电话号码有变化
				Integer clientid = null;
				if(deviceextend.getSn()!=null&&!"".equals(deviceextend.getSn())){
					jpql="select max(nc.clientid) from NewsClientinfo nc where nc.mencoding='"+deviceextend.getSn()+"'";
				}else{
					jpql="select (select max(nc.clientid) from TTelDevice ttd_n, NewsClientinfo nc where ttd_n.devicesn=nc.mencoding and ttd_n.telnumber=ttd.telnumber and ttd_n.devicesn=ttd.devicesn)" +
					" from TTelDevice ttd" +
					" where ttd.telnumber='"+deviceextend.getStel()+"' and ttd.telnumber!=''";
				}
				query = em.createQuery(jpql);
				if(query.getResultList().size()==0){
					//是否已经注册：未知
					deviceextend.setHasclientid(0);
				}else{
					clientid=(Integer)query.getResultList().get(0) ;
					if(clientid==null){
						//是否已经注册：否
						deviceextend.setHasclientid(2);
					}else{
						//是否已经注册：是
						deviceextend.setHasclientid(1);
					}
				}
				deviceextend.setClientid(clientid);

				//更新hasclientid和clientid
				jpql="UPDATE TDeviceExtend t SET t.hasclientid=:hasclientid,t.clientid=:clientid WHERE t.sid=:sid";
				query = em.createQuery(jpql);
				query.setParameter("hasclientid",deviceextend.getHasclientid());
				query.setParameter("clientid", deviceextend.getClientid());
				query.setParameter("sid", deviceextend.getSid());
				query.executeUpdate();
			}else{
				deviceextend.setHasclientid(deviceextend_before.getHasclientid());
				deviceextend.setClientid(deviceextend_before.getClientid());
			}
			
			if(deviceextend_before.getClientid()!=null||!deviceextend.getStel().equals(deviceextend_before.getStel())){
				//存在clientid||电话号码有变化,更新学生/员工信息与用户组的关系
				if(deviceextend_before.getClientid()!=null){
					//判断是否存在同一个学生/员工在同一组织结构中共用电话的情况(相同的clientid)
					jpql="SELECT clientid FROM TDeviceExtend t WHERE t.clientid=:clientid AND t.spcode.spcode=:spcode";
					query = em.createQuery(jpql);
					query.setParameter("clientid", deviceextend_before.getClientid());
					query.setParameter("spcode", deviceextend_before.getSpcode().getSpcode());
					if(query.getResultList().size()==0){
						//删除老的学生/员工信息与用户组的关系
						sql="delete from news_groupuserset where clientid=:clientid " +
						" and groupruleid=(select groupid from t_spcode_device where spcode=:spcode)";
						query=em.createNativeQuery(sql);
						query.setParameter("clientid", deviceextend_before.getClientid());
						query.setParameter("spcode", deviceextend_before.getSpcode().getSpcode());
						query.executeUpdate();
					}
				}
				if(deviceextend.getClientid()!=null){
					//先检查是否存在对应关系
					sql="select clientid from news_groupuserset where groupruleid=(select groupid from t_spcode_device where spcode=?)" +
							" and clientid=? ";
					query=em.createNativeQuery(sql);
					query.setParameter(1, deviceextend.getSpcode().getSpcode());
					query.setParameter(2, deviceextend.getClientid());
					if(query.getResultList().size()==0){
						//增加新的学生/员工信息与用户组的关系
						sql="insert into news_groupuserset (groupruleid,clientid) " +
						"values((select groupid from t_spcode_device where spcode=?),?)";
						query=em.createNativeQuery(sql);
						query.setParameter(1, deviceextend.getSpcode().getSpcode());
						query.setParameter(2, deviceextend.getClientid());
						query.executeUpdate();
					}
				}
			}
			
		}
		
		return resultb;
	}
	
	/**
	 * 删除学生/员工信息
	 * @param sid
	 * @return boolean
	 */
	public boolean deleteDeviceExtendByIds(String ids) {
		Boolean resultb=true;
//		try {
			//this.init();
			String[] idarys=ids.split(",");
			for(String id:idarys){
				Integer sid=Integer.valueOf(id);
				//判断是否存在同一个学生/员工在同一组织结构中共用电话的情况(相同的clientid)
//				this.sql="SELECT t.clientid FROM t_deviceextend t WHERE t.clientid=(select td.clientid from t_deviceextend td where td.sid="+sid+")" +
//						" AND t.spcode=(select td.spcode from t_deviceextend td where td.sid="+sid+") AND t.sid!="+sid+"";
				this.sql="SELECT count(*) FROM t_deviceextend t WHERE t.clientid=(select td.clientid from t_deviceextend td where td.sid="+sid+")" +
				" AND t.spcode=(select td.spcode from t_deviceextend td where td.sid="+sid+") AND t.sid!="+sid+"";
//				prep=conn.prepareStatement(sql);
//				prep.setInt(1, sid);
//				prep.setInt(2, sid);
//				prep.setInt(3, sid);
//				rs1=prep.executeQuery();
				;
//				if(!rs1.next()){
				if(this.getOneInt()==0){
					//删除学生/员工信息的clientd和用户组的对应关系
					this.sql="delete from news_groupuserset where clientid=(select td.clientid from t_deviceextend td where td.sid="+sid+") " +
							" and groupruleid=(select tsd.groupid from t_spcode_device tsd join t_deviceextend td on tsd.spcode=td.spcode where td.sid="+sid+")";
//					prep=conn.prepareStatement(sql);
//					prep.setInt(1, sid);
//					prep.setInt(2, sid);
//					prep.executeUpdate();
					this.em.createNativeQuery(sql).executeUpdate();
				}
				
				//删除学生/员工信息
				this.sql="delete from t_deviceextend where sid="+sid;
				this.em.createNativeQuery(sql).executeUpdate();
//				prep=conn.prepareStatement(sql);
//				prep.setInt(1, sid);
//				prep.executeUpdate();
				
			}
			
			//conn.commit(); 
//		} catch (Exception e) {
//			logger.error("deleteDeviceExtendById fail !!!" + e.getMessage());
//			e.printStackTrace();
//			resultb=false;
//			if(conn!=null){
//				try {
//					conn.rollback();
//				} catch (SQLException e1) {
//					e1.printStackTrace();
//				} 
//			}
//		} finally {
//			this.closeAll();
//		}
		return resultb;
	}
	
	public Object getSingleResult(Query query) {
		Object o =null;
		try {
			o = query.getSingleResult();
		} catch (NoResultException ex) {
			return null;
		} catch (NonUniqueResultException ex) {
			o = query.getResultList();
			return ((List) o).get(0);
		}
		return o;
	}
	
	/**
	 * 重新分配组织机构 
	 * 准备数据
	 * @param paramMap
	 * @return List<TDeviceExtend>
	 */
	public List<TDeviceExtend> assignSpcode_prepareList(DataMap paramMap){
		TDeviceExtend deviceextend=(TDeviceExtend) paramMap.get("deviceextend");
		String ids=(String) paramMap.get("ids");
		String oper=(String)paramMap.get("oper");
		List<TDeviceExtend> TDElist=new ArrayList<TDeviceExtend>();
		try {
			//this.init();
			TDeviceExtend deviceextend_D = null;
			if("isadd".equals(oper)||"iscopy".equals(oper)){
				String ids_temp="";
				String[] idarys=ids.split(",");
				for(String id:idarys){
					ids_temp+="'" + id + "',";
				}
				if (!ids_temp.equals("")) {
					ids_temp = ids_temp.substring(0, ids_temp.length() - 1);
				}
				sql="select sid,sname,stel,sn,spcode,job,sadress,smobile,hasclientid,clientid from t_deviceextend where sid in ("+ids_temp+")";

			}else if("isaddall".equals(oper)||"iscopyall".equals(oper)){
				StringBuffer sqlS = new StringBuffer("SELECT sid,sname,stel,sn,spcode,job,sadress,smobile,hasclientid,clientid FROM t_deviceextend WHERE 1=1 ");
				if(deviceextend!=null){
					if(deviceextend.getSname()!=null&& deviceextend.getSname().toString().trim().length() > 0){
						sqlS.append(" AND sname like '%"
								+ deviceextend.getSname().toString().trim() + "%'");
					}
					if(deviceextend.getStel()!=null&& deviceextend.getStel().toString().trim().length() > 0){
						sqlS.append(" AND stel like '%"
								+ deviceextend.getStel().toString().trim() + "%'");
					}
					if(deviceextend.getSpcode().getSpcode()!=null&& deviceextend.getSpcode().getSpcode().toString().trim().length() > 0){
						sqlS.append(" AND spcode='"
								+ deviceextend.getSpcode().getSpcode().toString().trim() + "'");
					}
				}
				sql=sqlS.toString();
			//	prep=conn.prepareStatement(sqlS.toString());
//				List<Object[]> temp=this.emr.createNativeQuery(sqlS.toString()).getResultList();
//				prep=conn.prepareStatement(sql);
//				rs1=prep.executeQuery();
//				while(rs1.next()){
//			for(Object[]o:temp)
//			{
//					deviceextend_D=new TDeviceExtend();
//					deviceextend_D.setSid(rs1.getInt(1));
//					deviceextend_D.setSname(rs1.getString(2));
//					deviceextend_D.setStel(rs1.getString(3));
//					deviceextend_D.setSn(rs1.getString(4));
//					deviceextend_D.setSpcode(new TSpcode(rs1.getString(5)));
//					deviceextend_D.setJob(rs1.getString(6));
//					deviceextend_D.setSadress(rs1.getString(7));
//					deviceextend_D.setSmobile(rs1.getString(8));
//					deviceextend_D.setHasclientid(rs1.getInt(9));
//					if(rs1.getInt(10)!=0){
//						deviceextend_D.setClientid(rs1.getInt(10));
//					}
//					TDElist.add(deviceextend_D);
//				}
			}
			List<Object[]> temp=this.em.createNativeQuery(sql).getResultList();
//			prep=conn.prepareStatement(sql);
//			rs1=prep.executeQuery();
//			while(rs1.next()){
		for(Object[]o:temp)
		{
//				deviceextend_D=new TDeviceExtend();
//				deviceextend_D.setSid(rs1.getInt(1));
//				deviceextend_D.setSname(rs1.getString(2));
//				deviceextend_D.setStel(rs1.getString(3));
//				deviceextend_D.setSn(rs1.getString(4));
//				deviceextend_D.setSpcode(new TSpcode(rs1.getString(5)));
//				deviceextend_D.setJob(rs1.getString(6));
//				deviceextend_D.setSadress(rs1.getString(7));
//				deviceextend_D.setSmobile(rs1.getString(8));
//				deviceextend_D.setHasclientid(rs1.getInt(9));
//				if(rs1.getInt(10)!=0){
//					deviceextend_D.setClientid(rs1.getInt(10));
//				}
				deviceextend_D.setSid(o[0]==null?0:Integer.valueOf(o[0].toString()));
				deviceextend_D.setSname(o[1]==null?"":o[1].toString());
				deviceextend_D.setStel(o[2]==null?"":o[2].toString());
				deviceextend_D.setSn(o[3]==null?"":o[3].toString());
				deviceextend_D.setSpcode(new TSpcode(o[4]==null?"":o[4].toString()));
				deviceextend_D.setJob(o[5]==null?"":o[5].toString());
				deviceextend_D.setSadress(o[6]==null?"":o[6].toString());
				deviceextend_D.setSmobile(o[7]==null?"":o[7].toString());
				deviceextend_D.setHasclientid(o[8]==null?0:Integer.valueOf(o[8].toString()));
				deviceextend_D.setClientid(o[9]==null?0:Integer.valueOf(o[9].toString()));
//				if(rs1.getInt(10)!=0){
//					deviceextend_D.setClientid(rs1.getInt(10));
//				}
				TDElist.add(deviceextend_D);
			}
//			conn.commit(); 
		} catch (Exception e) {
			logger.error("assignSpcode_prepareList fail !!!" + e.getMessage());
			e.printStackTrace();
			
		} 
		
		return TDElist;
	}
	
	/**
	 * 重新分配机构组织
	 * @param paramMap
	 * @return
	 */
	public boolean assignSpcode(DataMap paramMap) {		
		Boolean resultb=true;
		String oper=(String)paramMap.get("oper");
		String target_spcode=(String) paramMap.get("target_spcode");
		List<TDeviceExtend> TDElist=(List<TDeviceExtend>) paramMap.get("TDElist");
		for(TDeviceExtend deviceextend:TDElist){
			String before_spcode=deviceextend.getSpcode().getSpcode();
			deviceextend.setSpcode(new TSpcode(target_spcode));
			if("isadd".equals(oper)||"isaddall".equals(oper)){
				Integer before_sid=deviceextend.getSid();
				String jpql="DELETE FROM TDeviceExtend WHERE sid=:sid ";
				Query query = em.createQuery(jpql);
				query.setParameter("sid",before_sid);
				query.executeUpdate();
				jpql="SELECT t FROM TDeviceExtend t WHERE t.sname=:sname AND t.stel=:stel AND t.spcode.spcode=:spcode AND t.job=:job AND t.sadress=:sadress AND t.smobile=:smobile ";
				query = em.createQuery(jpql);
				query.setParameter("sname",deviceextend.getSname());
				query.setParameter("stel", deviceextend.getStel());
				query.setParameter("spcode", deviceextend.getSpcode().getSpcode());
				query.setParameter("job", deviceextend.getJob());
				query.setParameter("sadress", deviceextend.getSadress());
				query.setParameter("smobile", deviceextend.getSmobile());
				TDeviceExtend deviceextend_temp= (TDeviceExtend) getSingleResult(query);
				if(deviceextend_temp==null){
					deviceextend.setSid(null);
					em.persist(deviceextend);
					if(deviceextend.getClientid()!=null){
						//判断是否存在同一个学生/员工在同一组织结构中共用电话的情况(相同的clientid)
						jpql="SELECT clientid FROM TDeviceExtend t WHERE t.clientid=:clientid AND t.spcode.spcode=:spcode";
						query = em.createQuery(jpql);
						query.setParameter("clientid", deviceextend.getClientid());
						query.setParameter("spcode", before_spcode);
						if(query.getResultList().size()==0){
							//删除老的学生/员工信息与用户组的关系
							sql="delete from news_groupuserset where clientid=:clientid " +
							" and groupruleid=(select groupid from t_spcode_device where spcode=:spcode)";
							query=em.createNativeQuery(sql);
							query.setParameter("clientid", deviceextend.getClientid());
							query.setParameter("spcode", before_spcode);
							int iCode=query.executeUpdate();
						}
						
						//先检查是否存在对应关系
						sql="select clientid from news_groupuserset where groupruleid=(select groupid from t_spcode_device where spcode=?)" +
								" and clientid=? ";
						query=em.createNativeQuery(sql);
						query.setParameter(1, deviceextend.getSpcode().getSpcode());
						query.setParameter(2, deviceextend.getClientid());
						if(query.getResultList().size()==0){
							sql="insert into news_groupuserset (groupruleid,clientid) " +
									"values((select groupid from t_spcode_device where spcode=:spcode),:clientid)";
							query=em.createNativeQuery(sql);
							query.setParameter("spcode", deviceextend.getSpcode().getSpcode());
							query.setParameter("clientid", deviceextend.getClientid());
							query.executeUpdate();
						}
					}
				}
			}else if("iscopy".equals(oper)||"iscopyall".equals(oper)){
				String jpql="SELECT t FROM TDeviceExtend t WHERE t.sname=:sname AND t.stel=:stel AND t.spcode.spcode=:spcode AND t.job=:job AND t.sadress=:sadress AND t.smobile=:smobile ";
				Query query = em.createQuery(jpql);
				query.setParameter("sname",deviceextend.getSname());
				query.setParameter("stel", deviceextend.getStel());
				query.setParameter("spcode", deviceextend.getSpcode().getSpcode());
				query.setParameter("job", deviceextend.getJob());
				query.setParameter("sadress", deviceextend.getSadress());
				query.setParameter("smobile", deviceextend.getSmobile());
				TDeviceExtend deviceextend_temp= (TDeviceExtend) getSingleResult(query);
				if(deviceextend_temp==null){
					deviceextend.setSid(null);
					em.persist(deviceextend);
					if(deviceextend.getClientid()!=null){
						//判断是否存在同一个学生/员工在同一组织结构中共用电话的情况(相同的clientid)
						jpql="SELECT clientid FROM TDeviceExtend t WHERE t.clientid=:clientid AND t.spcode.spcode=:spcode";
						query = em.createQuery(jpql);
						query.setParameter("clientid", deviceextend.getClientid());
						query.setParameter("spcode", before_spcode);
						if(query.getResultList().size()==0){
							//删除老的学生/员工信息与用户组的关系
							sql="delete from news_groupuserset where clientid=:clientid " +
							" and groupruleid=(select groupid from t_spcode_device where spcode=:spcode)";
							query=em.createNativeQuery(sql);
							query.setParameter("clientid", deviceextend.getClientid());
							query.setParameter("spcode", before_spcode);
							query.executeUpdate();
						}
						//先检查是否存在对应关系
						sql="select clientid from news_groupuserset where groupruleid=(select groupid from t_spcode_device where spcode=?)" +
								" and clientid=? ";
						query=em.createNativeQuery(sql);
						query.setParameter(1, deviceextend.getSpcode().getSpcode());
						query.setParameter(2, deviceextend.getClientid());
						if(query.getResultList().size()==0){
							//增加新的学生/员工信息与用户组的关系
							sql="insert into news_groupuserset (groupruleid,clientid) " +"values((select groupid from t_spcode_device where spcode=?),?)";
							query=em.createNativeQuery(sql);
							query.setParameter(1, deviceextend.getSpcode().getSpcode());
							query.setParameter(2, deviceextend.getClientid());
							query.executeUpdate();
						}
					}
				}
				
			}
			
		}
		return resultb;
		
	}
	

	public String findSnByclientid(String clientid) {
		 sql = "SELECT mencoding FROM news_clientinfo WHERE clientid="+clientid;
	//	String mencoding="";
		try {
//			DataMap result = this.queryOneRow(sql);
//			mencoding = result.getMapData("mencoding");
			return this.getOneString();
		} catch (Exception e) {
			logger.error(" findSnByclientid fail !!!" +e.getMessage());
			return null;
		}
	}
	
	public boolean sendMsg(List<NewsSnmsg> snmsgList) {
		boolean resultb=true;
		try {
			//this.init();
			this.sql="insert into news_snmsg (sn,msg,hassend,msgtemplateid) values(?,?,0,-1)";
			//prep=conn.prepareStatement(sql);
		Query	query=em.createNativeQuery(sql);
		
			for(NewsSnmsg snmsg:snmsgList){
//				prep.setString(1, snmsg.getSn());
//				prep.setString(2, snmsg.getMsg());
//				prep.addBatch();
				query.setParameter(1, snmsg.getSn());
				query.setParameter(2, snmsg.getMsg());
				query.executeUpdate();
			}
		
		} catch (Exception e) {
			logger.error("sendMsg fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
		
	}
	
	 /**
	 * 更新的学生/员工信息clientid和hasclientid值 
	 * 固话用户信息模块调用
	 * @param deviceextend
	 * @return boolean
	 */
	public boolean updateDeviceExtendClientid(TDeviceExtend deviceextend){
		Integer hasclientid_before= deviceextend.getHasclientid();
		Integer clientid_before= deviceextend.getClientid();
		Boolean resultb=true;
		try {
			//this.init();
			Integer clientid = null;
			if(deviceextend.getSn()!=null&&!"".equals(deviceextend.getSn())){
				sql="select max(nc.clientid) from news_clientinfo nc where nc.mencoding='"+deviceextend.getSn()+"'";
				//prep=conn.prepareStatement(sql);
				
			}else{
				sql="select (select max(nc.clientid) from t_tel_device ttd_n, news_clientinfo nc where ttd_n.devicesn=nc.mencoding and ttd_n.telnumber=ttd.telnumber and ttd_n.devicesn=ttd.devicesn) " +
				" from t_tel_device ttd" +
				" where ttd.telnumber='"+deviceextend.getStel()+"' and ttd.telnumber!=''";
				//prep=conn.prepareStatement(sql);
				//prep.setString(1, deviceextend.getStel());
			}
			clientid=this.getOneInt();
//			rs1=prep.executeQuery();
//			if(rs1.next()){
//				clientid=rs1.getInt(1);
				if(clientid==0){
					clientid=null;
				}
				if(clientid==null){
					//是否已经注册：否
					deviceextend.setHasclientid(2);
				}else{
					//是否已经注册：是
					deviceextend.setHasclientid(1);
				}
//			}else{
//				//是否已经注册：未知
//				deviceextend.setHasclientid(0);
//			}
			deviceextend.setClientid(clientid);
			
			if(!(deviceextend.getHasclientid().equals(hasclientid_before)&&clientid_before==deviceextend.getClientid())){
				//删除失效的学生/员工信息与用户组的关系
				sql="delete from news_groupuserset where groupruleid =(select groupid from t_spcode_device where spcode =?) and clientid="+clientid_before+"";
				
				Query	query=em.createNativeQuery(sql);
				
			
					query.setParameter(1,  deviceextend.getSpcode().getSpcode());
				//	query.setParameter(2, snmsg.getMsg());
					query.executeUpdate();
//				prep=conn.prepareStatement(sql);
//				prep.setString(1, deviceextend.getSpcode().getSpcode());
//				try {
//					prep.executeUpdate();
//				} catch (java.sql.SQLException e) {
//					//Subquery returns more than 1 row 存在共用电话的情况，也就是共用clientid
//				}
				
				
				//更新hasclientid和clientid
				sql="UPDATE t_deviceextend t SET t.hasclientid=?,t.clientid="+deviceextend.getClientid()+" WHERE t.sid=?";
//				prep=conn.prepareStatement(sql);
//				prep.setInt(1, deviceextend.getHasclientid());
//				//prep.setInt(2, deviceextend.getClientid());	//deviceextend.getClientid()为null时 throw java.lang.NullPointerException
//				prep.setInt(2, deviceextend.getSid());
//				prep.executeUpdate();
				query=em.createNativeQuery(sql);
				query.setParameter(1, deviceextend.getHasclientid());
				query.setParameter(2, deviceextend.getSid());
				query.executeUpdate();
				
				if(deviceextend.getClientid()!=null){
					//先检查是否存在对应关系
					sql="select clientid from news_groupuserset where groupruleid=(select groupid from t_spcode_device where spcode='"+deviceextend.getSpcode().getSpcode()+"')" +
						" and clientid= "+deviceextend.getClientid();
//					prep=conn.prepareStatement(sql);
//					prep.setString(1, deviceextend.getSpcode().getSpcode());
//					prep.setInt(2, deviceextend.getClientid());
//					rs1=prep.executeQuery();
					int cid=this.getOneInt();
					if(cid==0){
					//if(!rs1.next()){
						//增加新的学生/员工信息与用户组的关系
//						sql="insert into news_groupuserset (groupruleid,clientid) " +"values((select groupid from t_spcode_device where spcode=?),?)";
//						prep=conn.prepareStatement(sql);
//						prep.setString(1, deviceextend.getSpcode().getSpcode());
//						prep.setInt(2, deviceextend.getClientid());
//						prep.executeUpdate();
					}
				}
				
			}
		} catch (Exception e) {
			logger.error("updateDeviceExtendClientid fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
			
		}
		return resultb;
	}

	public List<TSpcode> findAllSubOrganization(DataMap hashMap)
	{
		String spcode=(String)hashMap.get("spcode");
		String spcodeS=(String)hashMap.get("spcodeS");
		StringBuffer sqlS = new StringBuffer("SELECT n FROM TSpcode n ");
		if(spcode!=null){
			sqlS.append("where n.spcode='"+spcode+"'");
		}else if(spcodeS!=null){
			sqlS.append("where n.spcode in ("+spcodeS+")");
		}
		sqlS.append(" order by n.id ");
		List<TSpcode> l=new ArrayList<TSpcode>();
		try {
			Query query = em.createQuery(sqlS.toString());
			logger.info("findAllSubOrganization excute sql:"+sqlS.toString());
			l = query.getResultList();
			
		} catch (Exception e) {
			logger.error("findAllSubOrganization error!!!"+e.getMessage());
			e.printStackTrace();
		}
		
		return l;
	}
}
